/** Backup and Retore table data to and from a text file.

*/

import java.sql.*;
import java.io.*;
import java.awt.event.*;
import java.util.*;
import java.awt.*;
import javax.swing.*;
import javax.swing.border.*;

public class ImportAndExport extends CloseableJFrame implements ActionListener {
	// Database Connectivity objects, and Parameters.
	Connection cnn;
	Statement  stmt;
	ResultSet  res;
	ResultSetMetaData meta;
	String url = "jdbc:oracle:thin:@prover.cs.csubak.edu:1521:PROVERDB",
		 user="gradebook", passwd="c3m4p2s";
	Vector<String>  colNames ;

	// User Interface Componet Object
	// Components in North Panel:
	JPanel		plNorth		= new JPanel();
	JLabel		lbURL		= new JLabel("DB");
	JComboBox 	cbxURLs		= new JComboBox( );
	JLabel		lbUser		= new JLabel(" User");
	JTextField	tfUser		= new JTextField( "GRADEBOOK", 8);
	JLabel		lbPassword	= new JLabel(" Passwd");
	JPasswordField	pwfPassword	= new JPasswordField( "c3m4p2s", 5);

	// Components in South Panel:
	JPanel		plSouth		= new JPanel();
	JComboBox 	cbxTables	= new JComboBox( );
	JTextField	tfFileName	= new JTextField("tmp.txt", 22 );	
	JLabel		lblSeparator	= new JLabel ("   Field Separator");
	JTextField	tfSeparator	= new JTextField ("|", 2);
	JButton		btnBrowse		= new JButton ("Browse" );	

	
	// Components in East Panel:
	ButtonGroup	bgIEGroup	= new ButtonGroup();
	JPanel		plEast		= new JPanel();
	JRadioButton	rdbImport	= new JRadioButton("Import");
	JRadioButton	rdbExport	= new JRadioButton("Export", true);
	ButtonGroup	bgAOGroup	= new ButtonGroup();
	JRadioButton	rdbAppend	= new JRadioButton("Append");
	JRadioButton	rdbOverwrite	= new JRadioButton("Overwrite", true);
	JButton		btnRun 		= new JButton ("Run");

	// Componets in Central Panel:
	JPanel		plCenter	= new JPanel();
	JScrollPane 	scrlTextArea	= null;
	JTextArea	taContents	= new JTextArea(50, 50);

	// Vector<Vector<String>>	rows;	// Vector of Vectors (All columns of a row will be a vector)
	//JTable	grid;
	//JScrollPane srpnGrid = null;

	// GUI related objects
	Container c;
	//JLabel  lblSQL = new JLabel("SQL ", SwingConstants.RIGHT);
	// JTextField tfSQL = new JTextField("SELECT * FROM TEST", 40);
	// String strArr[] = new String[10]; 
	//JComboBox tfSQL = new JComboBox( );


	boolean connectionMade() {
	    try {
 		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		String url = (String) (cbxURLs.getSelectedItem() );
		String passwd = new String( pwfPassword.getPassword() );
		cnn = DriverManager.getConnection(url, tfUser.getText(), passwd.trim() );
		stmt = cnn.createStatement();
		return true;
	    } catch (SQLException e ) { JOptionPane.showMessageDialog(this, "Cannot make connection!");  return false;}
	}
	public ImportAndExport() {
		// Set content pane and components colors
		c = getContentPane(); c.setLayout(new BorderLayout());

		// Add components to Central Panel, and add Central panel to content pane.
		// taContents.setBackground( c.getBackground() );
		scrlTextArea = new JScrollPane( taContents );
		scrlTextArea.setPreferredSize( new Dimension( 530, 420) );
		plCenter.add( scrlTextArea );
		c.add( plCenter, BorderLayout.CENTER );

		// Add components to North Panel, and add North panel to content pane.
		cbxURLs.addItem( "jdbc:oracle:thin:@prover.cs.csubak.edu:1521:PROVERDB");
		cbxURLs.addItem( "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST");
	       	cbxURLs.setPreferredSize( new Dimension( 340, 20) );
		plNorth.add( lbURL ); plNorth.add(cbxURLs);
		plNorth.add( lbUser ); plNorth.add(tfUser);
		plNorth.add(lbPassword); plNorth.add( pwfPassword );
		c.add( plNorth, BorderLayout.NORTH);

		// Add components to South Panel, and add South panel to content pane.
		cbxTables.addItem( "All Tables");
	       	cbxTables.setPreferredSize( new Dimension( 140, 20) );
		plSouth.add( cbxTables );
		plSouth.add(new JLabel("") );
		plSouth.add( tfFileName );
		btnBrowse.setPreferredSize( new Dimension(78, 20));
		btnBrowse.addActionListener( this );
		plSouth.add( btnBrowse );
		plSouth.add( lblSeparator ) ;
		plSouth.add( tfSeparator );
		c.add( plSouth, BorderLayout.SOUTH);
		
		// Add components to East Panel, and add East panel to content pane.
		plEast.setBorder( new TitledBorder("Options"));
		plEast.setLayout( new GridLayout(0, 1) ) ;
		bgIEGroup.add( rdbImport ); bgIEGroup.add( rdbExport );
		plEast.add( rdbImport ) ;
		plEast.add( rdbExport) ;
		plEast.add( new JLabel ("") );   // fill a blank objet to separate two groups.
		bgAOGroup.add( rdbAppend); bgAOGroup.add( rdbOverwrite);
		plEast.add( rdbAppend) ;
		plEast.add( rdbOverwrite) ;
		for ( int i = 0; i < 10; i ++ ) plEast.add( new JLabel ("") );   // fill a blank objet to separate two groups.
		btnRun.addActionListener( this );
		btnRun.setPreferredSize( new Dimension(50, 20) ); plEast.add( btnRun ) ;
		c.add( plEast, BorderLayout.EAST);

		// Connect to database, and fill Table combobox.
		String passwd = new String( pwfPassword.getPassword() );
		cnn = DBUtil.connect( (String) (cbxURLs.getSelectedItem()),
				      tfUser.getText(), passwd );
		try { stmt = cnn.createStatement(); } catch (SQLException e) { }
		res  = DBUtil.getResultSet ( stmt, "SELECT TName FROM Tab WHERE TabType = 'TABLE' ORDER By TName");
		GUIUtil.fillComboBox(res, cbxTables );

		// c.add(pnSouth, BorderLayout.SOUTH);
		setSize(new Dimension(640, 520));
		setTitle("Import & Export Oracle Database");
		setVisible(true);

	}

	public static void main(String argv[]) {
		ImportAndExport ex4 = new ImportAndExport(); // get connected, and create stmt.
	}

//	public void paint(Graphics g) {
//		super.paint(g);
//	}

	public void actionPerformed (ActionEvent e) {
	    int colCnt = 0;
	    Object obj = e.getSource();
	  
	    if (obj == btnBrowse ) {
		   tfFileName.setText( GUIUtil.selectFile() );
		   // btnRun.setEnabled( true );
		   return;
	    } 
	    if ( obj == btnRun ) {
		    doImportAndExport();
		    return;
	    }
/*
		// String sql = ((String) tfSQL.getSelectedItem() ).trim();
		String sql = ((String) tfSQL.getText() ).trim();
	
		// execute a select or update statement?
		boolean selectedStatement = executeSQL(sql);
		if (! selectedStatement ) return;
		try { colCnt = meta.getColumnCount(); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); }

		rows = new Vector<Vector<String>> (); colNames = new Vector<String>();
		fillColumnNameVector(colNames, colCnt);
		fillRows(rows, colCnt);

		setTitle("Result of " + ((String) tfSQL.getText()).trim());
		// setTitle("Result of " + ((String) tfSQL.getSelectedItem()).trim());
		if (srpnGrid != null ) c.remove(srpnGrid);
		grid = new JTable(rows, colNames);
		srpnGrid = new JScrollPane(grid);
		c.add(srpnGrid, BorderLayout.CENTER);
		validate();
		//repaint();
*/
	}

	void doImportAndExport () {
	    boolean blExp = rdbExport.isSelected();
	    boolean blAppend = rdbAppend.isSelected();
	    String  tableName = ((String) cbxTables.getSelectedItem()).trim();
	    String  fileName =  tfFileName.getText().trim();

	    if ( blExp ) 
		    doExport( tableName, fileName, blAppend );
	    else
		    doImport( fileName );
	    return;
	}
	
	void doExport( String tableName, String fileName, boolean blAppend ) {
	    FileWriter fileWriter = null;
	    try {
	        if ( ! tableName.equals("All Tables") ) {
		    fileWriter = new FileWriter( fileName, blAppend );
		    exportTableToFile( tableName, fileWriter, tfSeparator.getText() );
	        } else {
		    int cnt = cbxTables.getItemCount();
		    fileWriter = new FileWriter( fileName );
		    for (int i = 1; i < cnt; i++ ) {
			tableName = ( String ) cbxTables.getItemAt(i) ; 
			exportTableToFile( tableName, fileWriter, tfSeparator.getText() );
		    }
		}
		fileWriter.close();
	    } catch ( IOException e ) { e.printStackTrace() ; }
	}

	void exportTableToFile( String tableName, FileWriter fwtr, String colSeparator ) {
	    int 	rowCount;

	    try {
	        taContents.append( tableName + " is being exported to "  + tfFileName.getText() + " ......\n");
	        validate();
	        res  = stmt.executeQuery( "SELECT * FROM " + tableName );
		meta = res.getMetaData();
		int  cnt = meta.getColumnCount();
		fwtr.write("\nTable Name" + colSeparator + tableName + colSeparator + cnt + "\n");
		rowCount = DBUtil.resultSetToFile( res, fwtr, colSeparator, cnt );
	   	fwtr.flush(); 
	       
		taContents.append( rowCount + " records of are exported.\n\n");
	        validate();

	    } catch ( SQLException e1 ) { e1.printStackTrace(); }
	      catch ( IOException e2 ) { e2.printStackTrace(); }
	}


	void doImport( String fileName ) {

	}

	boolean executeSQL(String sql) {
	return true;
/*
	   try {
		if ( sql.charAt(sql.length() -1) == ';' ) sql = sql.substring(0, sql.length() -1);
		boolean selectStatement = sql.substring(0, 6).toUpperCase().equals("SELECT");
		int     count = 0;

		if ( !selectStatement ) {
	       	       count = stmt.executeUpdate(sql);
		       System.out.printf("%d record(s) affected by '%s'\n", count, sql);
		       sql = "SELECT * FROM TEST";
		       tfSQL.setText( sql );
		       // return false;
		}	       
		res = stmt.executeQuery(sql);
		meta = res.getMetaData();
		// if ( tfSQL.getSelectedIndex() == -1 ) tfSQL.addItem( sql );
	   } catch (SQLException e) { System.err.println("\nError occured in executing SQL: \n\t" + sql + "\n");
	   				return false;
	   }
	   return true;
*/

	}

	void fillRows(Vector<Vector<String>> v, int colCnt) {
/*
	   try {

		while ( res.next() ) {
			Vector<String> t = new Vector<String>();
			for ( int i = 1; i <= colCnt; i ++ ) t.add( res.getString(i)) ;
			v.add( t);
		}	
	   } catch (SQLException e) { e.printStackTrace();  }
	     catch (Exception e2) { e2.printStackTrace();  }
*/
	}

	void fillColumnNameVector(Vector<String> v, int cnt) {
/*
	   try {
		for ( int i = 1; i <= cnt; i ++ ) v.add(meta.getColumnName(i));
	   } catch (SQLException e ) { e.printStackTrace(); }
*/
	
	}
	
	protected void finalize() {
/*
	   try {
		stmt.close();
		res.close();
		cnn.close();
	   } catch (SQLException e) {}
	   try { super.finalize(); } catch (Throwable e) { e.printStackTrace(); }
*/
	}
}
